package com.ljxt.dt.util.xhq;

import com.alibaba.druid.support.logging.Log;
import com.alibaba.druid.support.logging.LogFactory;
import com.alibaba.druid.util.*;
import com.ljxt.dt.dao.purchasingmanagementdao.ResultSetConsumers;
import com.ljxt.dt.util.DaoUtil;
import com.ljxt.dt.util.xhq.test.ContinuousTableMapping;
import com.ljxt.dt.util.xhq.test.TreeTest;
import jdk.nashorn.internal.objects.annotations.Where;
import sun.reflect.generics.tree.Tree;

import javax.sql.DataSource;
import java.io.Closeable;
import java.io.InputStream;
import java.io.PrintStream;
import java.net.URL;
import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * @author 徐浩钦
 * @version 1.0.0
 * @date 2020/5/11 17:32
 * @copyright 老九学堂T11
 */
public class JdbcUtils implements JdbcConstants{

    private static final Log LOG = LogFactory.getLog(com.alibaba.druid.util.JdbcUtils.class);
    private static final Properties DRIVER_URL_MAPPING = new Properties();
    private static Boolean mysql_driver_version_6 = null;

    public JdbcUtils() {
    }

    public static void close(Connection x) {
        if (x != null) {
            try {
                if (x.isClosed()) {
                    return;
                }

                x.close();
            } catch (Exception var2) {
                LOG.debug("close connection error", var2);
            }

        }
    }

    public static void close(Statement x) {
        if (x != null) {
            try {
                x.close();
            } catch (Exception var2) {
                LOG.debug("close statement error", var2);
            }

        }
    }

    public static void close(ResultSet x) {
        if (x != null) {
            try {
                x.close();
            } catch (Exception var2) {
                LOG.debug("close result set error", var2);
            }

        }
    }

    public static void close(Closeable x) {
        if (x != null) {
            try {
                x.close();
            } catch (Exception var2) {
                LOG.debug("close error", var2);
            }

        }
    }

    public static void close(Blob x) {
        if (x != null) {
            try {
                x.free();
            } catch (Exception var2) {
                LOG.debug("close error", var2);
            }

        }
    }

    public static void close(Clob x) {
        if (x != null) {
            try {
                x.free();
            } catch (Exception var2) {
                LOG.debug("close error", var2);
            }

        }
    }


    public static int executeUpdate(DataSource dataSource, String sql, Object... parameters) throws SQLException {
        return executeUpdate(dataSource, sql, Arrays.asList(parameters));
    }

    public static int executeUpdate(DataSource dataSource, String sql, List<Object> parameters) throws SQLException {
        Connection conn = null;

        int var4;
        try {
            conn = dataSource.getConnection();
            var4 = executeUpdate(conn, sql, parameters);
        } finally {
            close(conn);
        }

        return var4;
    }

    public static int executeUpdate(Connection conn, String sql, List<Object> parameters){
        PreparedStatement stmt = null;

        int updateCount= -1;
        try {
            System.out.println("sql = "+sql);
            System.out.println("pars = "+parameters);
            stmt = conn.prepareStatement(sql);
            setParameters(stmt, parameters);
            updateCount = stmt.executeUpdate();
        } catch (SQLException e){
            e.printStackTrace();
            System.out.println("死亡的 sql = " + sql);
            System.out.println("死亡的 parameters = " + parameters);

        }finally {
            close((Statement)stmt);
        }

        return updateCount;
    }

    public static void execute(DataSource dataSource, String sql, Object... parameters) throws SQLException {
        execute(dataSource, sql, Arrays.asList(parameters));
    }

    public static void execute(DataSource dataSource, String sql, List<Object> parameters) throws SQLException {
        Connection conn = null;

        try {
            conn = dataSource.getConnection();
            execute(conn, sql, parameters);
        } finally {
            close(conn);
        }

    }

    public static void execute(Connection conn, String sql) throws SQLException {
        execute(conn, sql, Collections.emptyList());
    }

    public static void execute(Connection conn, String sql, List<Object> parameters) throws SQLException {
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            setParameters(stmt, parameters);
            stmt.executeUpdate();
        } finally {
            close((Statement)stmt);
        }

    }

    public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql, Object... parameters) {
            return executeQuery(dataSource, sql, Arrays.asList(parameters));
    }

    public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql, List<Object> parameters) {
        Connection conn = null;

        List var4 = new ArrayList();
        try {
            conn = dataSource.getConnection();
            var4 = executeQuery(conn, sql, parameters);

        } catch (Exception e){
            e.printStackTrace();
        }finally {
            close(conn);
        }

        return var4;
    }

    public static List<Map<String, Object>> executeQuery(Connection conn, String sql, List<Object> parameters) throws SQLException {
        List<Map<String, Object>> rows = new ArrayList();
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            stmt = conn.prepareStatement(sql);
            setParameters(stmt, parameters);
            rs = stmt.executeQuery();
            ResultSetMetaData rsMeta = rs.getMetaData();

            while(rs.next()) {
                Map<String, Object> row = new LinkedHashMap();
                int i = 0;

                for(int size = rsMeta.getColumnCount(); i < size; ++i) {
                    String columName = rsMeta.getColumnLabel(i + 1);
                    Object value = rs.getObject(i + 1);
                    // 自定义方法 将下划线转驼峰
                    row.put(StringUtil.xiaHuaToTuoFeng(columName), value);
                }

                rows.add(row);
            }
        } finally {
            close(rs);
            close((Statement)stmt);
        }

        return rows;
    }

    private static void setParameters(PreparedStatement stmt, List<Object> parameters) throws SQLException {
        int i = 0;

        for(int size = parameters.size(); i < size; ++i) {
            Object param = parameters.get(i);
            stmt.setObject(i + 1, param);
        }

    }

    public static void insertToTable(DataSource dataSource, String tableName, Map<String, Object> data) throws SQLException {
        Connection conn = null;

        try {
            conn = dataSource.getConnection();
            insertToTable(conn, tableName, data);
        } finally {
            close(conn);
        }

    }

    public static void insertToTable(Connection conn, String tableName, Map<String, Object> data) throws SQLException {
        String sql = makeInsertToTableSql(tableName, data.keySet());
        List<Object> parameters = new ArrayList(data.values());
        execute((Connection)conn, sql, (List)parameters);
    }

    public static String makeInsertToTableSql(String tableName, Collection<String> names) {
        StringBuilder sql = (new StringBuilder()).append("insert into ").append(tableName).append("(");
        int nameCount = 0;

        for(Iterator var4 = names.iterator(); var4.hasNext(); ++nameCount) {
            String name = (String)var4.next();
            if (nameCount > 0) {
                sql.append(",");
            }

            sql.append(name);
        }

        sql.append(") values (");

        for(int i = 0; i < nameCount; ++i) {
            if (i != 0) {
                sql.append(",");
            }

            sql.append("?");
        }

        sql.append(")");
        return sql.toString();
    }

    public static <T> T executeQuery(DataSource dataSource, ResultSetConsumer<T> consumer, String sql, Object... parameters) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);

            for(int i = 0; i < parameters.length; ++i) {
                stmt.setObject(i + 1, parameters[i]);
            }

            rs = stmt.executeQuery();

            while(rs.next()) {

                if (consumer != null) {
                    T object = consumer.apply(rs);

                    Boolean branch = consumer.accept(object);

                    if(branch == null){
                        return null;
                    }else if(branch == true){

                    }else if(branch == false){
                        return object;
                    }
                }
            }

        } finally {
            close(rs);
            close((Statement)stmt);
            close(conn);
        }

        return null;
    }

    public static <T> void executeQuery(DataSource dataSource, ResultSetConsumers<T> consumer, String sql, Object... parameters) {
        List<Map<String, Object>> rows = new ArrayList();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // 连接对象
            conn = dataSource.getConnection();
            // sql执行对象
            stmt = conn.prepareStatement(sql);


            // 循环设置参数
            for(int i = 0; i < parameters.length; ++i) {
                stmt.setObject(i + 1, parameters[i]);
            }

            // 获取结果集对象
            rs = stmt.executeQuery();
            // 获取sql表的映射对象
            ResultSetMetaData rsMeta = rs.getMetaData();

            while(rs.next()) {
                // 创建一个用来映射一行表的map
                Map<String, Object> row = new LinkedHashMap();
                int i = 0;

                // 循环获取别名和值的对应并设置在 map 中
                for(int size = rsMeta.getColumnCount(); i < size; ++i) {
                    String columName = rsMeta.getColumnLabel(i + 1);
                    Object value = rs.getObject(i + 1);
                    // 自定义方法 将下划线转驼峰
                    row.put(StringUtil.xiaHuaToTuoFeng(columName), value);
                }

                // 如果接口不为空
                if (consumer != null) {
                    // 将结果存储在集合中
                    consumer.getRows().add(row);

                    //

                    // 则调用其申请的方法
                    T object = consumer.apply(rs,row);

                    boolean branch = consumer.accept(object);
                    if(branch == false){
                        return ;
                    }
                }
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(rs);
            close((Statement)stmt);
            close(conn);
        }

        return ;
    }

    public static <T,R> List<R> selectAll(DataSource dataSource, Map<String,Object[]> sqlAndParameters, ResultSetConsumers<T> consumer) throws SQLException {
        for (Map.Entry<String, Object[]> s : sqlAndParameters.entrySet()) {
            executeQuery(dataSource,s.getKey(),consumer,s.getValue());
        }
        return null;
    }

    public static List<String> showTables(Connection conn, String dbType) throws SQLException {
        if ("mysql".equals(dbType)) {
            return MySqlUtils.showTables(conn);
        } else if ("oracle".equals(dbType)) {
            return OracleUtils.showTables(conn);
        } else if ("postgresql".equals(dbType)) {
            return PGUtils.showTables(conn);
        } else {
            throw new SQLException("show tables dbType not support for " + dbType);
        }
    }

    public static String getCreateTableScript(Connection conn, String dbType) throws SQLException {
        return getCreateTableScript(conn, dbType, true, true);
    }

    public static String getCreateTableScript(Connection conn, String dbType, boolean sorted, boolean simplify) throws SQLException {
        if ("mysql".equals(dbType)) {
            return MySqlUtils.getCreateTableScript(conn, sorted, simplify);
        } else if ("oracle".equals(dbType)) {
            return OracleUtils.getCreateTableScript(conn, sorted, simplify);
        } else {
            throw new SQLException("getCreateTableScript dbType not support for " + dbType);
        }
    }

    public static boolean isMySqlDriver(String driverClassName) {
        return driverClassName.equals("com.mysql.jdbc.Driver") || driverClassName.equals("com.mysql.cj.jdbc.Driver") || driverClassName.equals("com.mysql.jdbc.");
    }

    static {
        try {
            ClassLoader ctxClassLoader = Thread.currentThread().getContextClassLoader();
            Properties property;
            if (ctxClassLoader != null) {
                for(Enumeration e = ctxClassLoader.getResources("META-INF/druid-driver.properties");
                    e.hasMoreElements(); DRIVER_URL_MAPPING.putAll(property)) {
                    URL url = (URL)e.nextElement();
                    property = new Properties();
                    InputStream is = null;

                    try {
                        is = url.openStream();
                        property.load(is);
                    } finally {
                        close((Closeable)is);
                    }
                }
            }
        } catch (Exception var9) {
            LOG.error("load druid-driver.properties error", var9);
        }

    }

    /**
     * 自定义方法
     * @param sql
     * @param pageIndex
     * @param pageSize
     * @param clazz
     * @param object
     * @param <T>
     * @return
     */
    public static <T> List<T> selectAll(String sql, Integer pageIndex, Integer pageSize, Class<T> clazz, Object ...object) {
        List<T> result = new ArrayList<>();

        if(StringUtil.isNullAndEmpty(sql)){
            throw  new RuntimeException("sql 语句不能为 null 或者 空串");
        }

        if(pageIndex != null && pageSize != null){
            sql += " LIMIT " + (pageIndex - 1) * pageSize +" , " + pageSize;
        }

        try {

            List<Map<String, Object>> lists = null;
            // 判断是否需要传参数
            if(object != null && object.length > 0){
                lists = JdbcUtils.executeQuery(DaoUtil.dataSource,sql,object);
            }else{
                lists = JdbcUtils.executeQuery(DaoUtil.dataSource,sql);
            }

            result = ClassUtil.listMapToListObj(lists, clazz);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return result;
    }


    /**
     * 重载上述方法
     * @param sql
     * @param clazz
     * @param object
     * @param <T>
     * @return
     */
    public static <T> List<T> selectAll(String sql, Class<T> clazz, Object ...object) {
        return selectAll(sql,null,null,clazz,object);
    }

    /**
     *
     * @param treeTest
     * @param continuousTableMapping
     */
    private static void testtest(TreeTest treeTest,ContinuousTableMapping continuousTableMapping){

        Object[] objects = treeTest.getMap().getValue();

        ArrayList<LinkedHashMap<String, Object>> contents = continuousTableMapping.getContent();
        for (LinkedHashMap<String, Object> content : contents) {
            ContinuousTableMapping tempc = new ContinuousTableMapping();

            //设置参数
            List<Object> pars = new ArrayList<>();
            for (Object o : objects) {
                pars.add(content.get(o));
            }

            tempc.setSql(treeTest.getMap().getKey());
            tempc.setParameter(pars);
            tempc.setContent(executeQuery1(DaoUtil.dataSource,tempc.getSql(),tempc.getParameter().toArray()));
            tempc.setFather(continuousTableMapping);
            continuousTableMapping.getChildLists().add(tempc);

            if(treeTest.getNext() != null){
                testtest(treeTest.getNext(),tempc);
            }
        }
        if(treeTest.getNext() == null){
            return;
        }
    }

    /**
     * 连续的表查询
     * @param dataSource 数据池
     * @param sqlAndParameter sql 和 参数的 map 映射
     * @return
     */
    public static ContinuousTableMapping continuousTableQuery(
            DataSource dataSource, LinkedHashMap<String,Object[]> sqlAndParameter){

        ContinuousTableMapping continuousTableMapping = new ContinuousTableMapping();

        // 用来存储临时
        TreeTest tt = new TreeTest();
        TreeTest temptt = null;
        for (Map.Entry<String, Object[]> stringEntry : sqlAndParameter.entrySet()) {
            if(temptt == null){
                temptt = tt;
                tt.setMap(stringEntry);
            }else{
                TreeTest temp = new TreeTest();
                temptt.setNext(temp);
                temp.setMap(stringEntry);
                temptt = temp;
            }
        }

        continuousTableMapping.setSql(tt.getMap().getKey());
        continuousTableMapping.setParameter(Arrays.asList(tt.getMap().getValue()));
        continuousTableMapping.setContent(executeQuery1(DaoUtil.dataSource,continuousTableMapping.getSql(),continuousTableMapping.getParameter().toArray()));
        testtest(tt.getNext(),continuousTableMapping);

        return continuousTableMapping;
    }

    /**
     * 配置连续表查询的查询方法
     * @param dataSource 连接池
     * @param sql sql 语句
     * @param parameters 参数数组
     * @return
     */
    private static ArrayList<LinkedHashMap<String, Object>> executeQuery1(DataSource dataSource, String sql, Object[] parameters) {
        ArrayList<LinkedHashMap<String, Object>> rows = new ArrayList();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // 连接对象
            conn = dataSource.getConnection();
            // sql执行对象
            stmt = conn.prepareStatement(sql);


            // 循环设置参数
            for(int i = 0; i < parameters.length; ++i) {
                stmt.setObject(i + 1, parameters[i]);
            }

            // 获取结果集对象
            rs = stmt.executeQuery();
            // 获取sql表的映射对象
            ResultSetMetaData rsMeta = rs.getMetaData();
            while(rs.next()) {
                // 创建一个用来映射一行表的map
                LinkedHashMap<String, Object> row = new LinkedHashMap();
                for (int i = 0; i < rsMeta.getColumnCount(); i++) {
                    String columName = rsMeta.getColumnLabel(i + 1);
                    Object value = rs.getObject(i + 1);
                    // 自定义方法 将下划线转驼峰
                    row.put(StringUtil.xiaHuaToTuoFeng(columName), value);
                }    
                rows.add(row);
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(rs);
            close((Statement)stmt);
            close(conn);
        }
        return rows;
    }


}
